﻿using System;
using System.Collections.Generic;
using System.Text;

namespace AutoWizard.Data
{
    public enum DBType
    {
        SQL2000 ,
        SQL2005
    }

    public class GDB
    {
        #region Dom2005

        static string Dom2005 = @"select DOMAIN_NAME,DATA_TYPE from INFORMATION_SCHEMA.DOMAINS";

        #endregion

        #region Dom2000

        static string Dom2000 = @"select DOMAIN_NAME,DATA_TYPE from master.INFORMATION_SCHEMA.DOMAINS";

        #endregion

        #region GetDomSQL

        static public string GetDomSQL(DBType dbType)
        {
            string ret = string.Empty;

            switch (dbType)
            {
                case DBType.SQL2000:
                    ret = Dom2000;
                    break;
                case DBType.SQL2005:
                    ret = Dom2005;
                    break;
            }

            return ret;
        }

        #endregion

        #region Table2005

        static string Table2005 = @"SELECT
                                TableName=O.name,
                                FieldName=C.name,
                                IsKey=ISNULL(IDX.PrimaryKey,N'0'),
                                FieldType=T.name,
                                Length=C.max_length,
                                FieldPrecision=C.precision,
                                Scale=C.scale,
                                CanNull=CASE WHEN C.is_nullable=1 THEN N'1'ELSE N'0' END,
                                DefaultValue=ISNULL(D.definition,N''),
								DescInfo = PFD.Value
                            FROM sys.columns C 
                                INNER JOIN sys.objects O 
                                ON C.[object_id]=O.[object_id] 
                                AND O.type='U' AND O.is_ms_shipped=0 INNER JOIN sys.types T
                                ON C.user_type_id=T.user_type_id LEFT JOIN sys.default_constraints D
                                ON C.[object_id]=D.parent_object_id
                                AND C.column_id=D.parent_column_id
                                AND C.default_object_id=D.[object_id]
                                LEFT JOIN sys.extended_properties PFD
                                ON PFD.class=1
                                AND C.[object_id]=PFD.major_id
                                AND C.column_id=PFD.minor_id
                                LEFT JOIN sys.extended_properties PTB
                                ON PTB.class=1
                                AND PTB.minor_id=0
                                AND C.[object_id]=PTB.major_id
                                LEFT JOIN 
                                (
                                    SELECT IDXC.[object_id], IDXC.column_id,
                                           Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending') 
                                               WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
                                           PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'1'ELSE N'0' END,
                                           IndexName=IDX.Name
                                    FROM sys.indexes IDX
                                           INNER JOIN sys.index_columns IDXC
                                    ON IDX.[object_id]=IDXC.[object_id]
                                        AND IDX.index_id=IDXC.index_id
                                        LEFT JOIN sys.key_constraints KC
                                    ON IDX.[object_id]=KC.[parent_object_id]
                                        AND IDX.index_id=KC.unique_index_id
                                        INNER JOIN 
                                        (
	                                        SELECT [object_id], Column_id, index_id=MIN(index_id)
	                                        FROM sys.index_columns
	                                        GROUP BY [object_id], Column_id
                                        ) IDXCUQ
                                    ON IDXC.[object_id]=IDXCUQ.[object_id]
                                        AND IDXC.Column_id=IDXCUQ.Column_id
                                        AND IDXC.index_id=IDXCUQ.index_id
                                ) IDX
                                ON C.[object_id]=IDX.[object_id]
                                    AND C.column_id=IDX.column_id
                                ORDER BY O.name";

        #endregion

        #region Table2000

        static string Table2000 = @"SELECT 
                                  d.name AS TableName,
                                  a.name AS FieldName, 
                                  CASE WHEN EXISTS
                                      (SELECT 1
                                     FROM dbo.sysindexes si INNER JOIN
                                           dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN
                                           dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN
                                           dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'
                                     WHERE sc.id = a.id AND sc.colid = a.colid) THEN '1' ELSE '0' END AS IsKey,
                                  b.name AS FieldType, 
                                  a.length AS Length, 
                                  COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS FieldPrecision, 
                                  ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS Scale,
                                  CASE WHEN a.isnullable = 1 THEN '1' ELSE '0' END AS CanNull, 
                                  ISNULL(e.text, '') AS DefaultValue,
                                  g.Value as DescInfo
                            FROM dbo.syscolumns a LEFT OUTER JOIN
                                  dbo.systypes b ON a.xtype = b.xusertype INNER JOIN
                                  dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND
                                  d.status >= 0 LEFT OUTER JOIN
                                  dbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOIN
                                  dbo.sysproperties g ON a.id = g.id AND a.colid = g.smallid AND
                                  g.name = 'MS_Description' LEFT OUTER JOIN
                                  dbo.sysproperties f ON d.id = f.id AND f.smallid = 0 AND
                                  f.name = 'MS_Description'
                            ORDER BY d.name";

        #endregion

        #region GetTableSQL

        static public string GetTableSQL(DBType dbType)
        {
            string ret = string.Empty;

            switch (dbType)
            {
                case DBType.SQL2000 :
                    ret = Table2000;
                    break;
                case DBType.SQL2005 :
                    ret = Table2005;
                    break;
            }

            return ret;
        }

        #endregion

        #region ForigenKey2005

        static string ForigenKey2005 = @"select TableName = o.name , ForergnKeyColumn = s.name ,
                    RefTableName = oref.name  , RefColumnName = sref.name 
                    from sys.foreign_key_columns f , sys.syscolumns s , sys.syscolumns sref , sys.objects o , sys.objects oref 
                    where f.parent_object_id = o.object_id and s.id= f.parent_object_id and s.colid = f.parent_column_id
                    and f.referenced_object_id = oref.object_id and sref.id= f.referenced_object_id and sref.colid = f.referenced_column_id";

        #endregion

        #region ForigenKey2000

        static string ForigenKey2000 = @"select TableName = o.name , 
	                ForergnKeyColumn = s.name ,
                    RefTableName = oref.name  , RefColumnName = sref.name 
                    from sysforeignkeys f , syscolumns s , syscolumns sref , sysobjects o , sysobjects oref 
                    where f.fkeyid = o.id and s.id= f.fkeyid and s.colid = f.fkey
                    and f.rkeyid = oref.id and sref.id= f.rkeyid and sref.colid = f.rkey";

        #endregion

        #region GetForigenKeySQL

        static public string GetForigenKeySQL(DBType dbType)
        {
            string ret = "";

            switch (dbType)
            {
                case DBType.SQL2005:
                    ret = ForigenKey2005;
                    break;
                case DBType.SQL2000:
                    ret = ForigenKey2000;
                    break;
            }

            return ret;
        }

        #endregion
    }
}
